package dao;

import model.Seat;
import util.DatabaseConnection;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 座位数据访问对象
 */
public class SeatDAO {

    /**
     * 获取所有座位 - 根据指定时间段获取实时状态
     */
    public List<Seat> getAllSeats() throws SQLException {
        return getSeatsWithStatus(null, null, null, -1, -1);
    }

    /**
     * 根据楼层获取座位 - 根据指定时间段获取实时状态
     */
    public List<Seat> getSeatsByFloor(int floor) throws SQLException {
        return getSeatsWithStatus(null, null, null, floor, -1);
    }

    /**
     * 根据筛选条件获取座位
     */
    public List<Seat> getFilteredSeats(int floor, int status) throws SQLException {
        StringBuilder sql = new StringBuilder("SELECT * FROM seats WHERE 1=1");

        if (floor != -1) {
            sql.append(" AND floor = ?");
        }
        if (status != -1) {
            sql.append(" AND base_status = ?");
        }
        sql.append(" ORDER BY floor, seat_number");

        List<Seat> seats = new ArrayList<>();

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql.toString())) {

            int paramIndex = 1;
            if (floor != -1) {
                stmt.setInt(paramIndex++, floor);
            }
            if (status != -1) {
                stmt.setInt(paramIndex++, status);
            }

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                seats.add(mapResultSetToSeat(rs));
            }
        }
        return seats;
    }

    /**
     * 根据ID获取座位
     */
    public Seat getSeatById(int seatId) throws SQLException {
        String sql = "SELECT * FROM seats WHERE seat_id = ?";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, seatId);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return mapResultSetToSeat(rs);
            }
        }
        return null;
    }

    /**
     * 插入新座位
     */
    public boolean insertSeat(Seat seat) throws SQLException {
        String sql = "INSERT INTO seats (seat_number, floor, area, base_status, equipment, has_computer, has_power_socket) "
                +
                "VALUES (?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, seat.getSeatNumber());
            stmt.setInt(2, seat.getFloor());
            stmt.setString(3, seat.getArea());
            stmt.setInt(4, seat.getStatus());
            stmt.setString(5, seat.getEquipment());
            stmt.setBoolean(6, seat.isHasComputer());
            stmt.setBoolean(7, seat.isHasPowerSocket());

            return stmt.executeUpdate() > 0;
        }
    }

    /**
     * 更新座位信息
     */
    public boolean updateSeat(Seat seat) throws SQLException {
        String sql = "UPDATE seats SET seat_number = ?, floor = ?, area = ?, base_status = ?, " +
                "equipment = ?, has_computer = ?, has_power_socket = ? WHERE seat_id = ?";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, seat.getSeatNumber());
            stmt.setInt(2, seat.getFloor());
            stmt.setString(3, seat.getArea());
            stmt.setInt(4, seat.getStatus());
            stmt.setString(5, seat.getEquipment());
            stmt.setBoolean(6, seat.isHasComputer());
            stmt.setBoolean(7, seat.isHasPowerSocket());
            stmt.setInt(8, seat.getSeatId());

            return stmt.executeUpdate() > 0;
        }
    }

    /**
     * 更新座位状态
     */
    public boolean updateSeatStatus(int seatId, int status) throws SQLException {
        String sql = "UPDATE seats SET base_status = ? WHERE seat_id = ?";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, status);
            stmt.setInt(2, seatId);

            return stmt.executeUpdate() > 0;
        }
    }

    /**
     * 删除座位
     */
    public boolean deleteSeat(int seatId) throws SQLException {
        String sql = "DELETE FROM seats WHERE seat_id = ?";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, seatId);
            return stmt.executeUpdate() > 0;
        }
    }

    /**
     * 根据楼层和房间获取座位 - 支持动态状态查询
     */
    public List<Seat> getSeatsByFloorAndRoom(int floor, int room) throws SQLException {
        return getSeatsWithStatus(null, null, null, floor, room);
    }

    /**
     * 获取所有空闲座位 - 根据时间段动态查询
     */
    public List<Seat> getAvailableSeats() throws SQLException {
        return getSeatsWithStatus(null, null, null, -1, -1).stream()
                .filter(seat -> seat.getStatus() == 0) // 只返回可用的座位
                .toList();
    }

    /**
     * 根据时间段获取空闲座位
     */
    public List<Seat> getAvailableSeats(java.sql.Date date, java.sql.Time startTime, java.sql.Time endTime)
            throws SQLException {
        return getSeatsWithStatus(date, startTime, endTime, -1, -1).stream()
                .filter(seat -> seat.getStatus() == 0) // 只返回在指定时间段可用的座位
                .toList();
    }

    /**
     * 根据楼层和时间段获取空闲座位
     */
    public List<Seat> getAvailableSeatsByFloor(int floor, java.sql.Date date, java.sql.Time startTime, java.sql.Time endTime)
            throws SQLException {
        return getSeatsWithStatus(date, startTime, endTime, floor, -1).stream()
                .filter(seat -> seat.getStatus() == 0) // 只返回在指定时间段和楼层可用的座位
                .toList();
    }

    /**
     * 获取指定楼层的所有房间号
     */
    public List<Integer> getRoomsByFloor(int floor) throws SQLException {
        String sql = "SELECT DISTINCT room FROM seats WHERE floor = ? ORDER BY room";
        List<Integer> rooms = new ArrayList<>();

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, floor);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                rooms.add(rs.getInt("room"));
            }
        }
        return rooms;
    }

    /**
     * 核心方法：根据日期和时间段获取座位的实时状态
     * 使用数据库存储过程GetSeatsWithStatus来查询
     */
    public List<Seat> getSeatsWithStatus(java.sql.Date date, java.sql.Time startTime, java.sql.Time endTime,
            int floor, int room) throws SQLException {
        List<Seat> seats = new ArrayList<>();

        // 如果没有指定时间，使用当前时间段
        if (date == null) {
            date = new java.sql.Date(System.currentTimeMillis());
        }
        if (startTime == null) {
            startTime = java.sql.Time.valueOf("00:00:00");
        }
        if (endTime == null) {
            endTime = java.sql.Time.valueOf("23:59:59");
        }

        String sql = "CALL GetSeatsWithStatus(?, ?, ?, ?, ?)";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setDate(1, date);
            stmt.setTime(2, startTime);
            stmt.setTime(3, endTime);
            stmt.setInt(4, floor);
            stmt.setInt(5, room);

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Seat seat = mapResultSetToSeatWithStatus(rs);
                seats.add(seat);
            }
        }

        return seats;
    }

    /**
     * 将ResultSet映射为Seat对象（包含动态状态）
     */
    private Seat mapResultSetToSeatWithStatus(ResultSet rs) throws SQLException {
        Seat seat = new Seat();
        seat.setSeatId(rs.getInt("seat_id"));
        seat.setSeatNumber(rs.getString("seat_number"));
        seat.setFloor(rs.getInt("floor"));
        seat.setRoom(rs.getInt("room"));
        seat.setSeatIndex(rs.getInt("seat_index"));
        seat.setArea(rs.getString("area"));
        seat.setStatus(rs.getInt("current_status")); // 使用动态计算的状态
        seat.setEquipment(rs.getString("equipment"));
        seat.setHasComputer(rs.getBoolean("has_computer"));
        seat.setHasPowerSocket(rs.getBoolean("has_power_socket"));
        return seat;
    }

    /**
     * 将ResultSet映射为Seat对象（传统方法，用于兼容）
     */
    private Seat mapResultSetToSeat(ResultSet rs) throws SQLException {
        Seat seat = new Seat();
        seat.setSeatId(rs.getInt("seat_id"));
        seat.setSeatNumber(rs.getString("seat_number"));
        seat.setFloor(rs.getInt("floor"));

        // 处理新字段，如果不存在则使用默认值
        try {
            seat.setRoom(rs.getInt("room"));
            seat.setSeatIndex(rs.getInt("seat_index"));
        } catch (SQLException e) {
            // 兼容旧数据结构
            seat.setRoom(1);
            seat.setSeatIndex(1);
        }

        seat.setArea(rs.getString("area"));

        // 根据字段名判断使用哪种状态
        try {
            seat.setStatus(rs.getInt("current_status"));
        } catch (SQLException e) {
            try {
                seat.setStatus(rs.getInt("base_status"));
            } catch (SQLException e2) {
                seat.setStatus(rs.getInt("status"));
            }
        }

        seat.setEquipment(rs.getString("equipment"));
        seat.setHasComputer(rs.getBoolean("has_computer"));
        seat.setHasPowerSocket(rs.getBoolean("has_power_socket"));
        return seat;
    }

    /**
     * 根据区域和状态筛选获取座位
     */
    public List<Seat> getFilteredSeatsByArea(String area, int status) throws SQLException {
        StringBuilder sql = new StringBuilder("SELECT * FROM seats WHERE 1=1");

        if (area != null) {
            sql.append(" AND area = ?");
        }
        if (status != -1) {
            sql.append(" AND base_status = ?");
        }
        sql.append(" ORDER BY area, seat_number");

        List<Seat> seats = new ArrayList<>();

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql.toString())) {

            int paramIndex = 1;
            if (area != null) {
                stmt.setString(paramIndex++, area);
            }
            if (status != -1) {
                stmt.setInt(paramIndex++, status);
            }

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Seat seat = mapResultSetToSeat(rs);
                seats.add(seat);
            }
        }

        return seats;
    }

    /**
     * 获取所有区域列表
     */
    public List<String> getAllAreas() throws SQLException {
        String sql = "SELECT DISTINCT area FROM seats ORDER BY area";
        List<String> areas = new ArrayList<>();

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                areas.add(rs.getString("area"));
            }
        }

        return areas;
    }
}
